﻿using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Net;

namespace S5_DBAccess
{
    //可以实现对Sql Server 数据库进行增删改查通用方法的操作
    public class DBHelper
    {
        //数据库连接串
        private static string _ConnectionString = string.Empty;
        /// <summary>
        /// 设置数据库连接串
        /// </summary>
        /// <param name="connectionString"></param>
        public static void SetConnectionString(string connectionString)
        {
            _ConnectionString = connectionString;
        }
        /// <summary>
        /// 通过sql语句查询并返回数据表格
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <returns></returns>
        public static DataTable SqlQuery(string sql, SqlParameter[] parameters)
        {
            DataTable dataTable = new DataTable();
            //数据库访问连接对象
            SqlConnection conn = new SqlConnection(_ConnectionString);
            //数据库访问的命令对象
            SqlCommand cmd = conn.CreateCommand();
            cmd.CommandText = sql;
            cmd.CommandType = System.Data.CommandType.Text;
            if (parameters != null && parameters.Length > 0)
            {
                cmd.Parameters.AddRange(parameters);
            }
            try
            {
                //打开数据库
                conn.Open();
                ////数据库查找器
                SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                ////通过执行sql语句查找数据，并填充到DataTable对象中
                adapter.Fill(dataTable);                
            }
            catch (Exception )
            {

                throw;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }

            return dataTable;
        }

        /// <summary>
        /// 执行sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public static int ExecuteSql(string sql)
        {            
            //数据库访问连接对象    
            SqlConnection conn = new SqlConnection(_ConnectionString);
            //事务对象
            SqlTransaction tran = null;
            int row = 0;
            try
            {
                //打开数据库
                conn.Open();
                //创建事务
                tran = conn.BeginTransaction();
                //数据库访问的命令对象
                SqlCommand cmd = conn.CreateCommand();
                cmd.Transaction = tran;
                cmd.CommandText = sql;
                cmd.CommandType = System.Data.CommandType.Text;

                //执行操作sql
                row = cmd.ExecuteNonQuery();
                //事务提交
                tran.Commit();
            }
            catch (Exception ex)
            {
                //事务回滚
                tran.Rollback();

                throw ex;
            }
            finally
            {
                if (conn.State != ConnectionState.Closed)
                {
                    conn.Close();
                }
            }
            return row;
        }
    }
}
